import pymysql
import sys
from docx import Document
import datetime


class MySQLMonitor():
    # 构造方法，接受数据库连接信息
    def __init__(self, host, port, user, password, database):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.conn = None
        self.cursor = None

    # 创建数据库连接
    def dbconn(self):
        try: 
            self.conn = pymysql.connect(
                host=self.host, 
                port=self.port, 
                user=self.user, 
                password=self.password, 
                database=self.database
            )
            self.cursor = self.conn.cursor()
        except Exception as e: 
            print('数据库连接失败')
            print(e)
            sys.exit()

    # 关闭数据库连接
    def close_conn(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()
    
    # 获取数据库运行状态数据，返回字典
    def get_status(self):
        status = {}
        
        # 确保数据库连接已建立
        if not self.conn:
            self.dbconn()
            
        try:
            # 获取数据库运行时长
            self.cursor.execute("show global status where Variable_name='Uptime'")
            uptime = self.cursor.fetchone()
            status['uptime'] = uptime[1]

            # 获取数据库当前连接数
            self.cursor.execute("show global status where Variable_name='Threads_connected'")
            threads_connected = self.cursor.fetchone()
            status['threads_connected'] = threads_connected[1]
            
            # 可以添加更多状态查询
            self.cursor.execute("show global status where Variable_name='Questions'")
            questions = self.cursor.fetchone()
            status['questions'] = questions[1]
            
            self.cursor.execute("show global status where Variable_name='Com_select'")
            com_select = self.cursor.fetchone()
            status['com_select'] = com_select[1]
            
        except Exception as e:
            print(f"获取状态失败: {e}")
            
        return status

    # 生成word报告 - 修正了方法定义
    def write_dict_to_word(self, data_dict, filename):
        # 创建一个新的Word文档
        doc = Document()
        
        # 添加标题
        newtime = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        doc.add_heading(f'MySQL数据库巡检报告 - {newtime}', level=1)
        
        # 添加副标题
        doc.add_paragraph(f'巡检时间: {newtime}')
        doc.add_paragraph(f'数据库: {self.database}@{self.host}:{self.port}')
        doc.add_paragraph('')  # 空行
        
        # 添加表格（行数为字典长度+1，列数为2）
        table = doc.add_table(rows=1, cols=2)
        table.style = 'Table Grid'
        
        # 设置表头
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '巡检项目'
        hdr_cells[1].text = '值'
        
        # 填充数据
        for key, value in data_dict.items():
            row_cells = table.add_row().cells
            row_cells[0].text = str(key)
            row_cells[1].text = str(value)
        
        # 保存文档
        doc.save(filename)
        print(f"字典已成功写入 {filename}")


if __name__ == '__main__':
    try:
        # 创建数据库连接对象
        monitor = MySQLMonitor('localhost', 3306, 'root', '123456', 'mysql')
        
        # 获取状态数据
        status_data = monitor.get_status()
        
        # 生成报告
        monitor.write_dict_to_word(status_data, 'mysql_status.docx')
        
    except Exception as e:
        print(f"程序执行失败: {e}")
    finally:
        # 确保关闭数据库连接
        if 'monitor' in locals():
            monitor.close_conn()